package com.wgworkshop.pems.repository;

import com.wgworkshop.pems.model.AttendanceCountByClassRecord;
import com.wgworkshop.pems.model.AttendanceStudentViewRecord;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import java.util.Date;

/**
 * 考勤库自定义接口实现类。
 * Created by wlc on 2017/8/31.
 */
public class AttendanceRepositoryImpl implements AttendanceRepositoryCustom {
    @PersistenceContext
    private EntityManager entityManager;

    @Override
    public Iterable<AttendanceStudentViewRecord> getAttendanceStudentView(int week, int classNum) {
        String sql = "SELECT s.id AS id, s.code AS code, s.name AS name, a.cnt AS count " +
                "FROM " +
                "(SELECT student_id, 2 * COUNT(id) AS cnt, course_date " +
                "FROM attendance WHERE DATE_FORMAT(course_date, '%w') = :week GROUP BY student_id) AS a " +
                "INNER JOIN class_arrange AS ca " +
                "ON a.student_id = ca.student_id " +
                "AND DATE_FORMAT(a.course_date, '%w') = DATE_FORMAT(ca.start_date, '%w') " +
                "AND ca.class_num = :classNum " +
                "AND ca.status = 1 " +
                "LEFT JOIN student AS s " +
                "ON a.student_id = s.id " +
                "WHERE s.status = 1 " +
                "ORDER BY s.code DESC ";
        Query query = entityManager.createNativeQuery(sql, "AttendanceStudentViewRecordMapping");
        query.setParameter("week", week);
        query.setParameter("classNum", classNum);
        return query.getResultList();
    }

    @Override
    public Iterable<AttendanceCountByClassRecord> getCountByClass(Date start, Date end, int week, int classNum) {
        String sql = "SELECT a.course_date AS course_date, COUNT(a.student_id) AS count " +
                "FROM class_arrange AS ca " +
                "LEFT JOIN attendance AS a " +
                "ON ca.student_id = a.student_id " +
                "AND DATE_FORMAT(ca.start_date, '%w') = DATE_FORMAT(a.course_date, '%w') " +
                "AND a.course_date BETWEEN :start AND :end " +
                "WHERE ca.status = 1 " +
                "AND DATE_FORMAT(ca.start_date, '%w') = :week " +
                "AND ca.class_num = :classNum " +
                "GROUP BY course_date " +
                "HAVING course_date IS NOT NULL " +
                "ORDER BY course_date ASC ";
        Query query = entityManager.createNativeQuery(sql, "AttendanceCountByClassRecordMapping");
        query.setParameter("start", start);
        query.setParameter("end", end);
        query.setParameter("week", week);
        query.setParameter("classNum", classNum);
        return query.getResultList();
    }

    @Override
    public Iterable<Object[]> getDetailByClass(Date start, Date end, int week, int classNum) {
        String sql = "SELECT a.course_date AS course_date, s.* " +
                "FROM class_arrange AS ca " +
                "LEFT JOIN attendance AS a " +
                "ON ca.student_id = a.student_id " +
                "AND DATE_FORMAT(ca.start_date, '%w') = DATE_FORMAT(a.course_date, '%w') " +
                "AND a.course_date BETWEEN :start AND :end " +
                "LEFT JOIN student s " +
                "ON ca.student_id = s.id " +
                "WHERE ca.status = 1 " +
                "AND s.status = 1 " +
                "AND DATE_FORMAT(ca.start_date, '%w') = :week " +
                "AND ca.class_num = :classNum " +
                "ORDER BY s.id ASC ";
        Query query = entityManager.createNativeQuery(sql, "AttendanceDetailByClassRecordMapping");
        query.setParameter("start", start);
        query.setParameter("end", end);
        query.setParameter("week", week);
        query.setParameter("classNum", classNum);
        return query.getResultList();
    }
}
